package cmpe.swe.purcom.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import cmpe.swe.purcom.dao.mapper.CommunityRowMapper;
import cmpe.swe.purcom.dao.mapper.EventRowMapper;
import cmpe.swe.purcom.dao.mapper.ServiceRowMapper;
import cmpe.swe.purcom.dao.mapper.StuffRowMapper;
import cmpe.swe.purcom.dao.mapper.UserRowMapper;
import cmpe.swe.purcom.model.Community;
import cmpe.swe.purcom.model.Event;
import cmpe.swe.purcom.model.EventAttendance;
import cmpe.swe.purcom.model.FeedItem;
import cmpe.swe.purcom.model.Service;
import cmpe.swe.purcom.model.ServiceContribution;
import cmpe.swe.purcom.model.Stuff;
import cmpe.swe.purcom.model.StuffContribution;
import cmpe.swe.purcom.model.User;

@Component
public class CommunityDao extends PcDao {

	/**
	 * Returns the new created community with its properties. These properties
	 * are added by using createPreparedStatement() function in the method.
	 * 
	 * @param c
	 *            a Community object
	 * @return a Community object contains properties
	 */
	public Community createCommunity(final Community c) {
		final String query = "INSERT INTO COMMUNITY (NAME, DESCRIPTION, STATUS, ENDDATE, TYPE) VALUES(?, ?, ?, ?, ?)";
		KeyHolder gkh = new GeneratedKeyHolder();

		// converting datetime (java.util.date) to (java.sql.timestamp)
		Date d = c.getEnddate();
		Timestamp ts = null;
		if (d != null) {
			ts = new Timestamp(d.getTime());
		}
		final Timestamp tsFinal = ts;

		this.getTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(query,
						Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, c.getName());
				ps.setString(2, c.getDescription());
				ps.setInt(3, c.getStatus());
				ps.setTimestamp(4, tsFinal);
				ps.setInt(5, c.getType());
				return ps;
			}
		}, gkh);

		Long newCommunityId = gkh.getKey().longValue();
		c.setId(newCommunityId);
		return c;
	}

	/**
	 * Return the record of communities in a List variable If there is no
	 * community record returns null
	 * 
	 * @return a List of communities
	 */
	public List<Community> getCommunities() {

		List<Community> communities = this.getTemplate().query(
				"SELECT * FROM COMMUNITY", new CommunityRowMapper());

		if (communities.isEmpty()) {
			return null;
		} else {
			return communities;
		}

	}

	/**
	 * Gets the list of communities with which user whose id is given is
	 * associated as creator or member
	 * 
	 * @param userId
	 *            id of user
	 * @return the list of associated communities with the user
	 */
	public List<Community> getMyCommunities(Long userId) {
		String query = "SELECT c.* FROM COMMUNITY c WHERE c.id in "
				+ " (SELECT uc.communityID FROM USER_COMMUNITY uc "
				+ " WHERE uc.userID = ?) ";

		List<Community> communities = this.getTemplate().query(query,
				new Object[] { userId }, new CommunityRowMapper());

		if (communities.isEmpty()) {
			return null;
		} else {
			return communities;
		}

	}

	/**
	 * Returns the community record with the gicen id.
	 * 
	 * @param id
	 *            a id variable is using for identifying the community
	 * @return a Community object
	 */
	public Community getCommunity(Long id) {
		List<Community> communities = this.getTemplate().query(
				"SELECT * FROM COMMUNITY c WHERE c.id = ?",
				new Object[] { id }, new CommunityRowMapper());

		if (communities.isEmpty()) {
			return null;
		} else {
			return communities.get(0);
		}

	}

	/**
	 * Returns the communities which are relevant the given name in a List
	 * variable
	 * 
	 * @param name
	 *            a string variable using for search in the community records
	 * @return a List of communities
	 */
	public List<Community> getCommunitiesByName(String name) {
		List<Community> communities = this.getTemplate().query(
				"SELECT * FROM COMMUNITY c WHERE c.name like ?",
				new Object[] { "%" + name + "%" }, new CommunityRowMapper());

		return communities;
	}

	/**
	 * Links the given user with the given community with regards to the role of
	 * the user (member or creator)
	 * 
	 * @param communityID
	 *            communityID used for community identification
	 * @param userID
	 *            userID used for user identification
	 * @param role
	 *            role of the user
	 */
	public void linkCommunityWithUser(final Long communityID,
			final Long userID, final String role) {
		final String query = "INSERT INTO USER_COMMUNITY (userID, communityID, ROLE) VALUES(?, ?, ?)";

		this.getTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(query,
						Statement.RETURN_GENERATED_KEYS);
				ps.setLong(1, userID);
				ps.setLong(2, communityID);
				ps.setString(3, role);

				return ps;
			}
		});
	}

	/**
	 * Returns the members of the given community in a List
	 * 
	 * @param communityID
	 *            communityID used for community identification
	 * @return a List variable
	 */
	public List<User> getCommunityMembers(Long communityID) {
		List<User> members = this
				.getTemplate()
				.query("SELECT u.* FROM USER u, USER_COMMUNITY uc WHERE uc.communityID=? AND u.id = uc.userID",
						new Object[] { communityID }, new UserRowMapper());

		if (members.isEmpty()) {
			return null;
		} else {
			return members;
		}
	}

	/**
	 * Return the membership and role information of the user in a Map object
	 * 
	 * @param communityID
	 *            communityID used for community identification
	 * @param userID
	 *            userID used for user identification
	 * @return a Map object
	 */
	public Map<String, Object> getMembership(Long communityID, Long userID) {

		Integer rowAmount = this
				.getTemplate()
				.queryForObject(
						"SELECT COUNT(*) FROM USER_COMMUNITY uc WHERE uc.communityID = ? AND uc.userID = ?",
						new Object[] { communityID, userID }, Integer.class);
		Map<String, Object> membership = new TreeMap<String, Object>();
		if (rowAmount < 1) {
			membership.put("isMember", false);
			return membership;
		} else {
			String role = this
					.getTemplate()
					.queryForObject(
							"SELECT uc.role FROM USER_COMMUNITY uc WHERE uc.communityID = ? AND uc.userID = ?",
							new Object[] { communityID, userID }, String.class);
			membership.put("isMember", true);
			membership.put("role", role);
			return membership;
		}

	}

	public void leaveCommunity(final Long communityID, final Long userID) {
		this.getTemplate()
				.update("DELETE FROM USER_COMMUNITY WHERE communityID = ? AND userID = ?",
						new Object[] { communityID, userID });
	}

	/*
	 * EVENT methods
	 */

	/**
	 * Returns the new created event with its properties These properties are
	 * set by using createPreparedStatement function in the method.
	 * 
	 * @param e
	 *            an Event object
	 * @return an Event object with its properties
	 */
	public Event createEvent(final Event e) {
		final String query = "INSERT INTO EVENT (NAME, DESCRIPTION, DATETIME, LOCATION, COMMUNITYID ) VALUES(?, ?, ?, ?, ?)";

		// converting datetime (java.util.date) to (java.sql.timestamp)
		Date d = e.getDatetime();
		Timestamp ts = null;
		if (d != null) {
			ts = new Timestamp(d.getTime());
		}
		final Timestamp tsFinal = ts;

		KeyHolder gkh = new GeneratedKeyHolder();

		this.getTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(query,
						Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, e.getName());
				ps.setString(2, e.getDescription());
				ps.setTimestamp(3, tsFinal);
				ps.setString(4, e.getLocation());
				ps.setLong(5, e.getCommunityid());

				return ps;
			}
		}, gkh);

		Long newEventId = gkh.getKey().longValue();
		e.setId(newEventId);
		return e;
	}

	/**
	 * Returns the events of the given community in a List
	 * 
	 * @param communityId
	 *            communityId used for community identification
	 * @return a List variable
	 */
	public List<Event> getEvents(Long communityId) {
		List<Event> events = this.getTemplate().query(
				"SELECT e.* FROM EVENT e WHERE e.communityID=?",
				new Object[] { communityId }, new EventRowMapper());
		return events;
	}

	/**
	 * Returns the event object which corresponds to given communityId and
	 * eventId
	 * 
	 * @param communityId
	 *            communityId used for community identification
	 * @param eventId
	 *            eventId used for event identification
	 * @return an Event object
	 */
	public Event getEvent(Long communityId, Long eventId) {
		List<Event> events = this.getTemplate().query(
				"SELECT e.* FROM EVENT e WHERE e.communityID = ? AND e.id = ?",
				new Object[] { communityId, eventId }, new EventRowMapper());
		if (events.isEmpty()) {
			return null;
		} else {
			return events.get(0);
		}
	}

	/**
	 * Links the given user with the given event with regards to the role of the
	 * user (member or creator)
	 * 
	 * @param eventID
	 *            eventID used for event identification
	 * @param userID
	 *            userID used for user identification
	 * @param role
	 *            role of the user
	 */
	public void linkEventWithUser(final Long eventID, final Long userID,
			final String role) {
		final String query = "INSERT INTO USER_EVENT (userID, eventID, ROLE) VALUES(?, ?, ?)";

		this.getTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(query,
						Statement.RETURN_GENERATED_KEYS);
				ps.setLong(1, userID);
				ps.setLong(2, eventID);
				ps.setString(3, role);

				return ps;
			}
		});
	}

	/*
	 * STUFF methods
	 */

	/**
	 * Returns the new created stuff with its properties These properties are
	 * set by using createPreparedStatement function in the method.
	 * 
	 * @param s
	 *            a Stuff object
	 * @return a Stuff object with its properties
	 */
	public Stuff createStuff(final Stuff s) {
		final String query = "INSERT INTO STUFF (AMOUNT, NAME, DESCRIPTION, LOCATION, DATETIME, COMMUNITYID) VALUES(?,?,?,?,?,?)";

		// converting datetime (java.util.date) to (java.sql.timestamp)
		Date d = s.getDatetime();
		Timestamp ts = null;
		if (d != null) {
			ts = new Timestamp(d.getTime());
		}
		final Timestamp tsFinal = ts;

		KeyHolder gkh = new GeneratedKeyHolder();

		this.getTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(query,
						Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, s.getAmountRequested());
				ps.setString(2, s.getName());
				ps.setString(3, s.getDescription());
				ps.setString(4, s.getLocation());
				ps.setTimestamp(5, tsFinal);
				ps.setLong(6, s.getCommunityid());

				return ps;
			}
		}, gkh);

		Long newStuffId = gkh.getKey().longValue();
		s.setId(newStuffId);
		return s;
	}

	/**
	 * Returns the Stuff of the given community in a List
	 * 
	 * @param communityId
	 *            communityId for community identification
	 * @return a List variable
	 */
	public List<Stuff> getStuffs(Long communityId) {
		List<Stuff> stuffs = this
				.getTemplate()
				.query("SELECT s.*, COALESCE(SUM(us.amountContributed),0) "
						+ " FROM (SELECT * FROM STUFF st WHERE st.communityID = ?) AS s "
						+ " LEFT JOIN USER_STUFF_CONTRIBUTION AS us ON s.id = us.stuffID "
						+ " GROUP BY s.id", new Object[] { communityId },
						new StuffRowMapper());
		return stuffs;
	}

	/**
	 * Returns the stuff object which corresponds to given communityId and
	 * stuffId
	 * 
	 * @param communityId
	 *            communityId for community identification
	 * @param stuffId
	 *            stuffId for stuff identification
	 * @return a Stuff object
	 */
	public Stuff getStuff(Long communityId, Long stuffId) {
		List<Stuff> stuffs = this
				.getTemplate()
				.query("SELECT s.*, COALESCE(SUM(us.amountContributed),0) "
						+ " FROM (SELECT * FROM STUFF st WHERE st.communityID = ? AND st.id = ?) AS s "
						+ " LEFT JOIN USER_STUFF_CONTRIBUTION AS us ON s.id = us.stuffID "
						+ " GROUP BY s.id",
						new Object[] { communityId, stuffId },
						new StuffRowMapper());
		if (stuffs.isEmpty()) {
			return null;
		} else {
			return stuffs.get(0);
		}
	}

	/**
	 * Links the given user with the given stuff with regards to the role of the
	 * user (member or creator)
	 * 
	 * @param stuffID
	 *            stuffId for stuff identification
	 * @param userID
	 *            userId for user identification
	 * @param role
	 *            role of the user
	 */
	public void linkStuffWithUser(final Long stuffID, final Long userID,
			final String role) {
		final String query = "INSERT INTO USER_STUFF (userID, stuffID, ROLE) VALUES(?, ?, ?)";

		this.getTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(query,
						Statement.RETURN_GENERATED_KEYS);
				ps.setLong(1, userID);
				ps.setLong(2, stuffID);
				ps.setString(3, role);

				return ps;
			}
		});
	}

	/**
	 * Returns a new StuffContribution object which has contribution
	 * information. This information are set by using createPreparedStatement()
	 * function in the method
	 * 
	 * @param sc
	 *            a StuffContribution object
	 * @return a StuffContribution object with its properties
	 */
	public StuffContribution contributeToStuff(final StuffContribution sc) {
		final String query = "INSERT INTO USER_STUFF_CONTRIBUTION (USERID, STUFFID, AMOUNTCONTRIBUTED, INSERTIONDATE, USERPHONE) VALUES(?,?,?,NOW(),?)";

		KeyHolder gkh = new GeneratedKeyHolder();

		this.getTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(query,
						Statement.RETURN_GENERATED_KEYS);

				ps.setLong(1, sc.getUserID());
				ps.setLong(2, sc.getActivityID());
				ps.setInt(3, sc.getAmountContributed());
				ps.setString(4, sc.getUserPhone());
				return ps;
			}
		}, gkh);

		Long newStuffContributionId = gkh.getKey().longValue();
		sc.setId(newStuffContributionId);
		return sc;

	}

	/*
	 * SERVICE methods
	 */

	/**
	 * Returns the new created service with its properties These properties are
	 * set by using createPreparedStatement function in the method.
	 * 
	 * @param s
	 *            a Service object
	 * @return a Service object with its properties
	 */
	public Service createService(final Service s) {
		final String query = "INSERT INTO SERVICE (NAME, DESCRIPTION, SERVICENUMBER, ENDDATETIME, LOCATION, COMMUNITYID) VALUES(?,?,?,?,?,?)";

		// converting datetime (java.util.date) to (java.sql.timestamp)
		Date d = s.getEndDateTime();
		Timestamp ts = null;
		if (d != null) {
			ts = new Timestamp(d.getTime());
		}
		final Timestamp tsFinal = ts;

		KeyHolder gkh = new GeneratedKeyHolder();

		this.getTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(query,
						Statement.RETURN_GENERATED_KEYS);

				ps.setString(1, s.getName());
				ps.setString(2, s.getDescription());
				ps.setInt(3, s.getServiceNumberRequested());
				ps.setTimestamp(4, tsFinal);
				ps.setString(5, s.getLocation());
				ps.setLong(6, s.getCommunityid());

				return ps;
			}
		}, gkh);

		Long newServiceId = gkh.getKey().longValue();
		s.setId(newServiceId);
		return s;
	}

	/**
	 * Returns the Service of the given community in a List
	 * 
	 * @param communityId
	 *            communityId for community identification
	 * @return a List variable
	 */
	public List<Service> getServices(Long communityId) {
		List<Service> services = this
				.getTemplate()
				.query("SELECT s.*, COALESCE(SUM(us.amountContributed),0) "
						+ " FROM (SELECT * FROM SERVICE se WHERE se.communityID = ?) AS s "
						+ " LEFT JOIN USER_SERVICE_CONTRIBUTION AS us ON s.id = us.serviceID"
						+ " GROUP BY s.id", new Object[] { communityId },
						new ServiceRowMapper());
		return services;
	}

	/**
	 * Returns the services with regards to given communityId and serviceId in a
	 * List
	 * 
	 * @param communityId
	 *            communityId for community identification
	 * @param serviceId
	 *            serviceId for service identification
	 * @return a List variable
	 */
	public Service getService(Long communityId, Long serviceId) {
		List<Service> services = this
				.getTemplate()
				.query("SELECT s.*, COALESCE(SUM(us.amountContributed),0) "
						+ " FROM (SELECT * FROM SERVICE se WHERE se.communityID = ? AND se.id = ?) AS s "
						+ " LEFT JOIN USER_SERVICE_CONTRIBUTION AS us ON s.id = us.serviceID"
						+ " GROUP BY s.id",
						new Object[] { communityId, serviceId },
						new ServiceRowMapper());
		if (services.isEmpty()) {
			return null;
		} else {
			return services.get(0);
		}
	}

	/**
	 * Links the given user with the given service with regards to the role of
	 * the user (member or creator)
	 * 
	 * @param serviceID
	 *            serviceID for service identification
	 * @param userID
	 *            userID for user identification
	 * @param role
	 *            role of the user
	 */
	public void linkServiceWithUser(final Long serviceID, final Long userID,
			final String role) {
		final String query = "INSERT INTO USER_SERVICE (userID, serviceID, ROLE) VALUES(?, ?, ?)";

		this.getTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(query,
						Statement.RETURN_GENERATED_KEYS);
				ps.setLong(1, userID);
				ps.setLong(2, serviceID);
				ps.setString(3, role);

				return ps;
			}
		});
	}

	/**
	 * Returns a new ServiceContribution object which has contribution
	 * information. This information are set by using createPreparedStatement()
	 * function in the method
	 * 
	 * @param sc
	 *            a ServiceContribution object
	 * @return a new ServiceContibution object with its properties
	 */
	public ServiceContribution contributeToService(final ServiceContribution sc) {
		final String query = "INSERT INTO USER_SERVICE_CONTRIBUTION (USERID, SERVICEID, AMOUNTCONTRIBUTED, INSERTIONDATE, USERPHONE) VALUES(?,?,?,NOW(),?)";

		KeyHolder gkh = new GeneratedKeyHolder();

		this.getTemplate().update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(query,
						Statement.RETURN_GENERATED_KEYS);

				ps.setLong(1, sc.getUserID());
				ps.setLong(2, sc.getActivityID());
				ps.setInt(3, sc.getAmountContributed());
				ps.setString(4, sc.getUserPhone());
				return ps;
			}
		}, gkh);

		Long newServiceContributionId = gkh.getKey().longValue();
		sc.setId(newServiceContributionId);
		return sc;

	}

	/*
	 * FEEDS methods
	 */

	/**
	 * Returns the contributions within stuff whose id is given
	 * 
	 * @param stuffId
	 *            id of stuff
	 * @return List of stuff contributions
	 */
	public List<StuffContribution> getActivityFeedOfStuff(Long stuffId) {
		List<StuffContribution> stuffContributions = this
				.getTemplate()
				.query("SELECT usc.*, CONCAT(u.name,CONCAT(' ',u.surname)) as userName "
						+ " FROM USER_STUFF_CONTRIBUTION usc "
						+ " LEFT JOIN USER u ON usc.userID = u.id "
						+ " WHERE usc.stuffID = ? ORDER BY usc.insertionDate DESC",
						new Object[] { stuffId },
						new RowMapper<StuffContribution>() {

							@Override
							public StuffContribution mapRow(ResultSet rs,
									int rowNum) throws SQLException {
								StuffContribution stuffContribution = new StuffContribution();
								stuffContribution.setInsertionDate(rs
										.getTimestamp("insertionDate"));
								stuffContribution.setUserID(rs
										.getLong("userID"));
								stuffContribution.setUserName(rs
										.getString("userName"));
								stuffContribution.setActivityID(rs
										.getLong("stuffID"));

								stuffContribution.setId(rs.getLong("id"));
								stuffContribution.setAmountContributed(rs
										.getInt("amountContributed"));
								stuffContribution.setUserPhone(rs
										.getString("userPhone"));
								return stuffContribution;
							}

						});
		return stuffContributions;
	}

	/**
	 * Returns the contributions within service whose id is given
	 * 
	 * @param serviceId
	 *            id of service
	 * @return List of service contributions
	 */
	public List<ServiceContribution> getActivityFeedOfService(Long serviceId) {
		List<ServiceContribution> serviceContributions = this
				.getTemplate()
				.query("SELECT usc.*, CONCAT(u.name,CONCAT(' ',u.surname)) as userName "
						+ " FROM USER_SERVICE_CONTRIBUTION usc "
						+ " LEFT JOIN USER u ON usc.userID = u.id "
						+ " WHERE usc.serviceID = ? ORDER BY usc.insertionDate DESC",
						new Object[] { serviceId },
						new RowMapper<ServiceContribution>() {

							@Override
							public ServiceContribution mapRow(ResultSet rs,
									int rowNum) throws SQLException {
								ServiceContribution serviceContribution = new ServiceContribution();
								serviceContribution.setInsertionDate(rs
										.getTimestamp("insertionDate"));
								serviceContribution.setUserID(rs
										.getLong("userID"));
								serviceContribution.setUserName(rs
										.getString("userName"));
								serviceContribution.setActivityID(rs
										.getLong("serviceID"));

								serviceContribution.setId(rs.getLong("id"));
								serviceContribution.setAmountContributed(rs
										.getInt("amountContributed"));
								serviceContribution.setUserPhone(rs
										.getString("userPhone"));
								return serviceContribution;
							}

						});
		return serviceContributions;
	}

	/**
	 * Returns the attendances within event whose id is given
	 * 
	 * @param eventId
	 *            id of event
	 * @return List of event attendances
	 */
	public List<EventAttendance> getActivityFeedOfEvent(Long eventId) {
		List<EventAttendance> eventAttendances = this
				.getTemplate()
				.query("SELECT ue.*,CONCAT(u.name,CONCAT(' ',u.surname)) as userName "
						+ " FROM USER_EVENT ue "
						+ " LEFT JOIN USER u ON ue.userID=u.id "
						+ " WHERE ue.eventID= ? ORDER BY ue.insertionDate DESC;",
						new Object[] { eventId },
						new RowMapper<EventAttendance>() {

							@Override
							public EventAttendance mapRow(ResultSet rs,
									int rowNum) throws SQLException {
								EventAttendance eventAttendance = new EventAttendance();
								eventAttendance.setInsertionDate(rs
										.getTimestamp("insertionDate"));
								eventAttendance.setUserID(rs.getLong("userID"));
								eventAttendance.setUserName(rs
										.getString("userName"));
								eventAttendance.setActivityID(rs
										.getLong("eventID"));

								eventAttendance.setRole(rs.getString("role"));
								return eventAttendance;
							}

						});
		return eventAttendances;
	}

	/**
	 * Returns the contributions/attendances of the community whose id is given
	 * 
	 * @param communityId
	 *            id of community
	 * @return List of feed items
	 */
	public List<FeedItem> getActivityFeedOfCommunity(Long communityId) {
		List<FeedItem> stuffContributions = this
				.getTemplate()
				.query("SELECT usc.*, s.name as stuffName, "
						+ " CONCAT(u.name,CONCAT(' ',u.surname)) as userName "
						+ " FROM USER_STUFF_CONTRIBUTION usc "
						+ " LEFT JOIN STUFF s ON s.id = usc.stuffID "
						+ " LEFT JOIN USER u ON u.id = usc.userID "
						+ " WHERE s.communityID = ? ORDER BY usc.insertionDate DESC",
						new Object[] { communityId },
						new RowMapper<FeedItem>() {

							@Override
							public FeedItem mapRow(ResultSet rs, int rowNum)
									throws SQLException {
								StuffContribution stuffContribution = new StuffContribution();
								stuffContribution.setInsertionDate(rs
										.getTimestamp("insertionDate"));
								stuffContribution.setUserID(rs
										.getLong("userID"));
								stuffContribution.setUserName(rs
										.getString("userName"));
								stuffContribution.setActivityID(rs
										.getLong("stuffID"));
								stuffContribution.setActivityName(rs
										.getString("stuffName"));

								stuffContribution.setId(rs.getLong("id"));
								stuffContribution.setAmountContributed(rs
										.getInt("amountContributed"));
								stuffContribution.setUserPhone(rs
										.getString("userPhone"));
								return stuffContribution;
							}

						});

		List<FeedItem> serviceContributions = this
				.getTemplate()
				.query("SELECT usc.*, s.name as serviceName, "
						+ " CONCAT(u.name,CONCAT(' ',u.surname)) as userName "
						+ " FROM USER_SERVICE_CONTRIBUTION usc "
						+ " LEFT JOIN SERVICE s ON s.id = usc.serviceID "
						+ " LEFT JOIN USER u ON u.id = usc.userID "
						+ " WHERE s.communityID = ? ORDER BY usc.insertionDate DESC",
						new Object[] { communityId },
						new RowMapper<FeedItem>() {

							@Override
							public FeedItem mapRow(ResultSet rs, int rowNum)
									throws SQLException {
								ServiceContribution serviceContribution = new ServiceContribution();
								serviceContribution.setInsertionDate(rs
										.getTimestamp("insertionDate"));
								serviceContribution.setUserID(rs
										.getLong("userID"));
								serviceContribution.setUserName(rs
										.getString("userName"));
								serviceContribution.setActivityID(rs
										.getLong("serviceID"));
								serviceContribution.setActivityName(rs
										.getString("serviceName"));

								serviceContribution.setId(rs.getLong("id"));
								serviceContribution.setAmountContributed(rs
										.getInt("amountContributed"));
								serviceContribution.setUserPhone(rs
										.getString("userPhone"));
								return serviceContribution;
							}

						});

		List<FeedItem> eventAttendances = this
				.getTemplate()
				.query("SELECT ue.*, e.name as eventName, "
						+ " CONCAT(u.name,CONCAT(' ',u.surname)) as userName "
						+ " FROM USER_EVENT ue "
						+ " LEFT JOIN EVENT e ON e.id = ue.eventID "
						+ " LEFT JOIN USER u ON ue.userID=u.id "
						+ " WHERE e.communityID = ? ORDER BY ue.insertionDate DESC",
						new Object[] { communityId },
						new RowMapper<FeedItem>() {

							@Override
							public FeedItem mapRow(ResultSet rs, int rowNum)
									throws SQLException {
								EventAttendance eventAttendance = new EventAttendance();
								eventAttendance.setInsertionDate(rs
										.getTimestamp("insertionDate"));
								eventAttendance.setUserID(rs.getLong("userID"));
								eventAttendance.setUserName(rs
										.getString("userName"));
								eventAttendance.setActivityID(rs
										.getLong("eventID"));
								eventAttendance.setActivityName(rs
										.getString("eventName"));

								eventAttendance.setRole(rs.getString("role"));
								return eventAttendance;
							}

						});

		stuffContributions.addAll(serviceContributions);
		stuffContributions.addAll(eventAttendances);

		Collections.sort(stuffContributions, Collections.reverseOrder());
		return stuffContributions;
	}

	/**
	 * Returns the contributions/attendances of communities with which somehow
	 * user whose id is given is associated
	 * 
	 * @param userId
	 *            id of user
	 * @return List of feed items
	 */
	public List<FeedItem> getActivityFeedOfMyCommunities(Long userId) {
		List<FeedItem> stuffContributions = this
				.getTemplate()
				.query("SELECT usc.*, s.name as stuffName, c.name as communityName, c.id as communityID, "
						+ " CONCAT(u.name,CONCAT(' ',u.surname)) as userName "
						+ " FROM USER_STUFF_CONTRIBUTION usc "
						+ " LEFT JOIN STUFF s ON s.id = usc.stuffID "
						+ " LEFT JOIN USER u ON u.id = usc.userID "
						+ " LEFT JOIN COMMUNITY c ON s.communityID = c.id "
						+ " WHERE s.communityID IN (SELECT uc.communityID "
						+ " FROM USER_COMMUNITY uc WHERE uc.userID = ?) ORDER BY usc.insertionDate DESC",
						new Object[] { userId }, new RowMapper<FeedItem>() {

							@Override
							public FeedItem mapRow(ResultSet rs, int rowNum)
									throws SQLException {
								StuffContribution stuffContribution = new StuffContribution();
								stuffContribution.setInsertionDate(rs
										.getTimestamp("insertionDate"));
								stuffContribution.setUserID(rs
										.getLong("userID"));
								stuffContribution.setUserName(rs
										.getString("userName"));
								stuffContribution.setActivityID(rs
										.getLong("stuffID"));
								stuffContribution.setActivityName(rs
										.getString("stuffName"));
								stuffContribution.setCommunityID(rs
										.getLong("communityID"));
								stuffContribution.setCommunityName(rs
										.getString("communityName"));

								stuffContribution.setId(rs.getLong("id"));
								stuffContribution.setAmountContributed(rs
										.getInt("amountContributed"));
								stuffContribution.setUserPhone(rs
										.getString("userPhone"));
								return stuffContribution;
							}

						});

		List<FeedItem> serviceContributions = this
				.getTemplate()
				.query("SELECT usc.*, s.name as serviceName, c.name as communityName, c.id as communityID,  "
						+ " CONCAT(u.name,CONCAT(' ',u.surname)) as userName "
						+ " FROM USER_SERVICE_CONTRIBUTION usc "
						+ " LEFT JOIN SERVICE s ON s.id = usc.serviceID "
						+ " LEFT JOIN USER u ON u.id = usc.userID "
						+ " LEFT JOIN COMMUNITY c ON s.communityID = c.id "
						+ " WHERE s.communityID IN (SELECT uc.communityID "
						+ " FROM USER_COMMUNITY uc WHERE uc.userID = ?) ORDER BY usc.insertionDate DESC",
						new Object[] { userId }, new RowMapper<FeedItem>() {

							@Override
							public FeedItem mapRow(ResultSet rs, int rowNum)
									throws SQLException {
								ServiceContribution serviceContribution = new ServiceContribution();
								serviceContribution.setInsertionDate(rs
										.getTimestamp("insertionDate"));
								serviceContribution.setUserID(rs
										.getLong("userID"));
								serviceContribution.setUserName(rs
										.getString("userName"));
								serviceContribution.setActivityID(rs
										.getLong("serviceID"));
								serviceContribution.setActivityName(rs
										.getString("serviceName"));
								serviceContribution.setCommunityID(rs
										.getLong("communityID"));
								serviceContribution.setCommunityName(rs
										.getString("communityName"));

								serviceContribution.setId(rs.getLong("id"));
								serviceContribution.setAmountContributed(rs
										.getInt("amountContributed"));
								serviceContribution.setUserPhone(rs
										.getString("userPhone"));
								return serviceContribution;
							}

						});

		List<FeedItem> eventAttendances = this
				.getTemplate()
				.query("SELECT ue.*, e.name as eventName,  c.name as communityName, c.id as communityID,  "
						+ " CONCAT(u.name,CONCAT(' ',u.surname)) as userName "
						+ " FROM USER_EVENT ue "
						+ " LEFT JOIN EVENT e ON e.id = ue.eventID "
						+ " LEFT JOIN USER u ON ue.userID=u.id "
						+ " LEFT JOIN COMMUNITY c ON e.communityID = c.id "
						+ " WHERE e.communityID IN (SELECT uc.communityID "
						+ " FROM USER_COMMUNITY uc WHERE uc.userID = ?) ORDER BY ue.insertionDate DESC",
						new Object[] { userId }, new RowMapper<FeedItem>() {

							@Override
							public FeedItem mapRow(ResultSet rs, int rowNum)
									throws SQLException {
								EventAttendance eventAttendance = new EventAttendance();
								eventAttendance.setInsertionDate(rs
										.getTimestamp("insertionDate"));
								eventAttendance.setUserID(rs.getLong("userID"));
								eventAttendance.setUserName(rs
										.getString("userName"));
								eventAttendance.setActivityID(rs
										.getLong("eventID"));
								eventAttendance.setActivityName(rs
										.getString("eventName"));
								eventAttendance.setCommunityID(rs
										.getLong("communityID"));
								eventAttendance.setCommunityName(rs
										.getString("communityName"));

								eventAttendance.setRole(rs.getString("role"));
								return eventAttendance;
							}

						});

		stuffContributions.addAll(serviceContributions);
		stuffContributions.addAll(eventAttendances);

		Collections.sort(stuffContributions, Collections.reverseOrder());
		return stuffContributions;
	}

}
